﻿using MiniExcelLibs;
using MiniExcelLibs.OpenXml;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using ZhiQianWinform.Vos;
using Zian.WinForm.Framework.Services;

namespace ZhiQianWinform.Services
{
    public class UserAccountNetCurdService : BaseNetCurdService<UserAccount>
    {
        protected override bool DoDelete(UserAccount t)
        {
            throw new NotImplementedException();
        }

        protected override bool DoDeleteAll()
        {
            throw new NotImplementedException();
        }

        /// <summary>
        ///A1 ID	Name	BoD	Age	VIP	Email	Points
        ///  10000	子安	2024/1/1	18	TRUE	1304023381@qq.com	345235
        ///  10001	阿紫	2024/1/2	22	TRUE	1304023382@qq.com	2421342
        ///  10002	若非	2024/1/3	34	FALSE	1304023383@qq.com	345235
        ///  10003	莫非	2024/1/4	11	TRUE	1304023384@qq.com	2421342
        ///  10004	明月心	2024/1/5	56	TRUE	1304023385@qq.com	345235
        ///  10005	秋雨	2024/1/6	18	FALSE	1304023386@qq.com	2421342
        ///  10006	萧萧	2024/1/7	22	TRUE	1304023387@qq.com	345235
        ///  10007	乔芳	2024/1/8	34	FALSE	1304023388@qq.com	2421342
        ///  10008	东东	2024/1/9	4	FALSE	1304023389@qq.com	345235
        ///  						
        ///  						
        ///A13 主键 姓名  日期 年龄  是否VIP 电子邮件    返点
        ///  10000	子安	2024/1/1	18	TRUE	1304023381@qq.com	345235
        ///  10001	阿紫	2024/1/2	22	TRUE	1304023382@qq.com	2421342
        ///  						
        ///  						
        ///  						
        ///  						
        ///A20 ID Name    BoD Age VIP Email   Points
        ///  10000	子安	2024/1/1	18	TRUE	1304023381@qq.com	345235
        ///  10001	阿紫	2024/1/2	22	TRUE	1304023382@qq.com	2421342
        ///  10002	若非	2024/1/3	34	FALSE	1304023383@qq.com	345235
        /// </summary>
        /// <returns></returns>
        protected override List<UserAccount> DoGetAllData()
        {
            string path = Path.Combine(Environment.CurrentDirectory, "Resources", "Excels", "UserAccount.xlsx");
            // 1.同步读取
            //var rows1 = MiniExcel.Query<UserAccount>(path).ToList();
            // 2.同步读取 sheet2
            //var rows2 = MiniExcel.Query<UserAccount>(path, "Sheet1").ToList();
            // 3.同步读取 sheet1 A20开始
            //var rows3 = MiniExcel.Query<UserAccount>(path, "Sheet1", ExcelType.XLSX, "A20").ToList();
            using (var stream = File.OpenRead(path))
            {
                // 4.异步读取 从第一行开始读取，集合的属性是A、B、C、D......
                var rows4 = stream.Query().ToList(); // row[0].B = Name
                // 5.异步读取 跳过第一行开始读取
                var rows5 = stream.Query(true).ToList(); // row[0].Name = 子安
                // 6.异步读取 A13 开始
                var rows6 = stream.Query(false, "Sheet1", ExcelType.XLSX, "A13").ToList(); // row[0].B = 姓名
                // 7.异步读取 A13 开始
                var rows7 = stream.Query(true, "Sheet1", ExcelType.XLSX, "A13").ToList(); // row[0].姓名 = 子安
            }
            // 8.查询所有 Sheet 名称跟数据
            var sheetNames = MiniExcel.GetSheetNames(path);
            foreach (var sheetName in sheetNames)
            {
                var rows8 = MiniExcel.Query(path, sheetName: sheetName);
            }
            // 9.查询所有栏(列) 第一行
            var columns9 = MiniExcel.GetColumns(path);
            // 10.查询所有栏(列) 第二行
            var columns10 = MiniExcel.GetColumns(path, useHeaderRow: true);
            // 11.Dynamic Query 转成 IDictionary<string,object> 数据 遍历
            foreach (IDictionary<string, object> row in MiniExcel.Query(path))
            {

            }
            // 12.Dynamic Query 转成 IDictionary<string,object> 数据 Cast
            var rows12 = MiniExcel.Query(path).Cast<IDictionary<string, object>>();
            // 13.Query 读 Excel 返回 DataTable
            var table13 = MiniExcel.QueryAsDataTable(path, true, "Sheet1", ExcelType.XLSX, "A13");
            // 14. 合并的单元格填充
            var config14 = new OpenXmlConfiguration()
            {
                FillMergedCells = true
            };
            var rows14 = MiniExcel.Query(path, sheetName: "Sheet2", configuration: config14);
            // 15. 读取大文件硬盘缓存 (Disk-Base Cache - SharedString)
            var config15 = new OpenXmlConfiguration { EnableSharedStringCache = false };
            var rows15 = MiniExcel.Query(path, sheetName: "Sheet2", configuration: config15);
            using (var stream = File.OpenRead(path))
            {
                var streamRows = stream.Query<UserAccount>("Sheet2", ExcelType.XLSX, "A1");
                List<UserAccount> users = streamRows.ToList();
                return users;
            }
        }

        protected override bool DoSave(UserAccount t)
        {
            throw new NotImplementedException();
        }

        protected override bool DoUpdate(UserAccount t)
        {
            throw new NotImplementedException();
        }
    }
}
